package com.dao.impl;
import com.dao.MemberDao;
import com.entity.Member;
import com.exception.CustomerException;
import com.result.QueryResult;
import com.util.DBUtil;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class MemberDaoImpl implements MemberDao {
    @Override
    public Member SelectByNum(Integer Num) {
        try {

            String sql = "select `num`,`name`,`sex`,`birthday`,`phone`," +
                    "`cpoint`,`jointime` from member where num=?";
            PreparedStatement preparedStatement = DBUtil.createPreparedStatement(sql);
            preparedStatement.setInt(1, Num);

            ResultSet resultSet = DBUtil.execQuery(preparedStatement);
            // 循环滚动游标
            if (resultSet.next()) {
                Member member=new Member();
                member.setNum(resultSet.getInt("num"));
                member.setName(resultSet.getString("name"));
                member.setSex(resultSet.getString("sex"));
                member.setBirthday(resultSet.getDate("birthday"));
                member.setPhone(resultSet.getString("phone"));
                member.setCpoint(resultSet.getInt("cpoint"));
                member.setJointime(resultSet.getDate("jointime"));
                // 把获取了字段特征的对象添加到集合中
                return  member;
            }
            return null;
        }catch (Exception e){
            e.printStackTrace();
            throw new CustomerException("查询失败!");
        }
    }

    @Override
    public List<Member> selectAll() throws SQLException, ClassNotFoundException {
        String sql = "select `num`,`name`,`sex`,`birthday`,`phone`," +
                "`cpoint`,`jointime` from member";
        PreparedStatement preparedStatement = DBUtil.createPreparedStatement(sql);
        ResultSet resultSet = DBUtil.execQuery(preparedStatement);
        List<Member> memberList = new ArrayList<>();
        // 循环滚动游标
        while(resultSet.next()){
            Member member=new Member();
            member.setNum(resultSet.getInt("num"));
            member.setName(resultSet.getString("name"));
            member.setSex(resultSet.getString("sex"));
            member.setBirthday(resultSet.getDate("birthday"));
            member.setPhone(resultSet.getString("phone"));
            member.setCpoint(resultSet.getInt("cpoint"));
            member.setJointime(resultSet.getDate("jointime"));
            // 把获取了字段特征的对象添加到集合中
            memberList.add(member);
        }
        DBUtil.close();
        return memberList;
    }

    @Override
    public QueryResult selectByName(String name){
        try {
            QueryResult queryResult = new QueryResult();
            String sql = "select `num`,`name`,`sex`,`birthday`,`phone`," +
                    "`cpoint`,`jointime` from member where name=? ";
            PreparedStatement preparedStatement = DBUtil.createPreparedStatement(sql);
            preparedStatement.setString(1, name);

            ResultSet resultSet = DBUtil.execQuery(preparedStatement);
            List<Member> memberList = new ArrayList<>();
            // 循环滚动游标
            while (resultSet.next()) {
                Member member = new Member();
                member.setNum(resultSet.getInt("num"));
                member.setName(resultSet.getString("name"));
                member.setSex(resultSet.getString("sex"));
                member.setBirthday(resultSet.getDate("birthday"));
                member.setPhone(resultSet.getString("phone"));
                member.setCpoint(resultSet.getInt("cpoint"));
                member.setJointime(resultSet.getDate("jointime"));
                // 把获取了字段特征的对象添加到集合中
                memberList.add(member);
                System.out.println(member);
            }
            queryResult.setList(memberList);

            //2.查询总记录数
            String sql2 = "select count(*) from member where name=?";

            PreparedStatement preparedStatement2 = DBUtil.createPreparedStatement(sql2);
            preparedStatement2.setString(1, name);
            ResultSet resultSet2 = DBUtil.execQuery(preparedStatement2);

            if (resultSet2.next()) {
                queryResult.setCount(resultSet2.getLong(1));
            }
            DBUtil.close();
            return queryResult;
        }catch (Exception e){
            e.printStackTrace();
            throw new CustomerException("按名称查询失败");
        }
    }

    @Override
    public QueryResult selectByPage(Integer offset, Integer size) {
        try {
            QueryResult queryResult = new QueryResult();

            //1.查询页面数据
            String sql = "select `num`,`name`,`sex`,`birthday`,`phone`," +
                    "`cpoint`,`jointime` from member limit ?,?";
            PreparedStatement preparedStatement = DBUtil.createPreparedStatement(sql);

            //设置分页条件
            preparedStatement.setInt(1, offset);
            preparedStatement.setInt(2, size);


            ResultSet resultSet = DBUtil.execQuery(preparedStatement);
            List<Member> memberList = new ArrayList<>();
            // 循环滚动游标
            while (resultSet.next()) {
                Member member=new Member();
                member.setNum(resultSet.getInt("num"));
                member.setName(resultSet.getString("name"));
                member.setSex(resultSet.getString("sex"));
                member.setBirthday(resultSet.getDate("birthday"));
                member.setPhone(resultSet.getString("phone"));
                member.setCpoint(resultSet.getInt("cpoint"));
                member.setJointime(resultSet.getDate("jointime"));
                // 把获取了字段特征的对象添加到集合中
                memberList.add(member);
            }

            queryResult.setList(memberList);

            //2.查询总记录数
            String sql2="select count(*) from member";
            PreparedStatement preparedStatement2 = DBUtil.createPreparedStatement(sql2);
            ResultSet resultSet2 = DBUtil.execQuery(preparedStatement2);

            if(resultSet2.next()){
                queryResult.setCount(resultSet2.getLong(1));
            }
            DBUtil.close();
            return queryResult;

        }catch (Exception e){
            e.printStackTrace();
            throw new CustomerException("查询失败!");
        }
    }

    @Override
    public Integer insert(Member member) {
        try {

            String insert = "insert into member value(null,?,?,?,?,?,?) ";
            PreparedStatement preparedStatement = DBUtil.createPreparedStatement(insert);
            // 给name赋值
            preparedStatement.setString(1,member.getName());
            preparedStatement.setString(2,member.getSex());
            preparedStatement.setDate(3,new java.sql.Date(member.getBirthday().getTime()));
            preparedStatement.setString(4,member.getPhone());
            preparedStatement.setInt(5,member.getCpoint());
            preparedStatement.setDate(6,new java.sql.Date(member.getJointime().getTime()));
            int i = DBUtil.execUpdate(preparedStatement);

            DBUtil.close();
            return i;
        }catch (Exception e){
            e.printStackTrace();
            throw new CustomerException("添加失败!");
        }
    }

    @Override
    public void update(Member member) {
        try {
            String sql = "update member set `name`=?,`sex`=?,`birthday`=?,`phone`=?,`cpoint`=?,`jointime`=? where num=?";
            PreparedStatement preparedStatement = DBUtil.createPreparedStatement(sql);
            preparedStatement.setString(1,member.getName());
            preparedStatement.setString(2,member.getSex());
            preparedStatement.setDate(3,new java.sql.Date(member.getBirthday().getTime()));
            preparedStatement.setString(4,member.getPhone());
            preparedStatement.setInt(5,member.getCpoint());
            preparedStatement.setDate(6,new java.sql.Date(member.getJointime().getTime()));
            preparedStatement.setInt(7,member.getNum());
            int i = DBUtil.execUpdate(preparedStatement);
        }catch (Exception e){
            e.printStackTrace();
            throw new CustomerException("修改失败!");
        }
    }

    @Override
    public Integer delete(Integer num) {
        try {
            String sql = "delete from member where num=?";
            PreparedStatement preparedStatement = DBUtil.createPreparedStatement(sql);
            preparedStatement.setInt(1,num);
            DBUtil.execUpdate(preparedStatement);
        }catch (Exception e){
            e.printStackTrace();
            throw new CustomerException("删除失败!");
        }
        return null;
    }
    }

